Don't include rows if data is unavailable
Hello, I am trying to get some factor and price data for different companies that have ESG scores available as well. My code looks like:
df, err = ek.get_data("SCREEN(U(IN(Equity(active,public,primary))/*UNV:Public*/), IN(TR.ExchangeCountryCode,""SE""), TR.TRESGScore(Period=FY0)>0, CURN=USD)",
["TR.PriceClose", "TR.PriceClose.Date", "TR.EBITMarginPercent", "TR.PretaxROAPercent", "TR.FreeOperatingCashFlowExclDividends","TR.RevenueSmartEstLastYrGrowth" , "TR.FreeCashFlow", "TR.WACCBeta"],
{'SDate': '2010-01-01', 'EDate': '2022-01-29','Frq':'M', 'SORTD': 'date'})
The only problem is that some companies don't have price data for all periods, but the empty periods are still included as NaN. Is there a easy way to make so that if there is no price data at date n, that whole row is not included.
Thanks in advance!
Best Answer
-
hi @student210
To investigate this, RIC "BMAX.ST", which is one of the results of this screening query, was picked to check the output dataframe, please see it in detail below.
- There're rows with NaN and <NA> in the output dataframe (see screenshot 1 in the reply due to the limit of image per each comment)
- First, about the TR.PriceClose of BMAX.ST, its data's been available since its IPO date
To remove the row before that, you may update the SDate and EDate Parameters or use the code belowdf3 = df3[df3['Date'] != '']
- Next, to clarify how the output dataframe is returned, the date of each field is included as its parameter. It's shown that each row doesn't represent each date but fields are merged into the dataframe without joining each field's date
So I'd like to suggest you do the step below
- get the list of RIC from the screener criteria
df1, err = ek.get_data("SCREEN(U(IN(Equity(active,public,primary))/*UNV:Public*/), IN(TR.ExchangeCountryCode,""SE""), TR.TRESGScore(Period=FY0)>0, CURN=USD)", "TR.RIC")
rics = df1["RIC"].to_list() - then use the list of RICs above as input instruments to get data from each set of data that return the same date-set (you may check this using CodeCreator app in Eikon or ask content specialist via MyRefinitivto clarify the content)
df2, err = ek.get_data(rics,
["TR.PriceClose", "TR.PriceClose.Date"],
{'SDate': '2010-01-01', 'EDate': '2022-01-29','Frq':'M', 'SORTD': 'date'})
df2 - use the dataframe from the previous step and join them together if needed
Hope this could help, please tell me in case you have any further questions
2
Answers
-
screenshot of the dataframe mentioned in step 1)
category of each field can be checked using CodeCreator app in Eikon or Refinitiv workspace
0
Categories
- All Categories
- 6 AHS
- 37 Alpha
- 161 App Studio
- 4 Block Chain
- 4 Bot Platform
- 16 Connected Risk APIs
- 47 Data Fusion
- 30 Data Model Discovery
- 608 Datastream
- 1.3K DSS
- 577 Eikon COM
- 4.9K Eikon Data APIs
- 7 Electronic Trading
- Generic FIX
- 7 Local Bank Node API
- Trading API
- 2.7K Elektron
- 1.3K EMA
- 236 ETA
- 519 WebSocket API
- 33 FX Venues
- 10 FX Market Data
- 1 FX Post Trade
- 1 FX Trading - Matching
- 12 FX Trading – RFQ Maker
- 5 Intelligent Tagging
- 2 Legal One
- 20 Messenger Bot
- 2 Messenger Side by Side
- 9 ONESOURCE
- 7 Indirect Tax
- 59 Open Calais
- 264 Open PermID
- 39 Entity Search
- 2 Org ID
- PAM
- PAM - Logging
- 8.4K Private Comments
- 6 Product Insight
- Project Tracking
- ProView
- ProView Internal
- 20 RDMS
- 1.4K Refinitiv Data Platform
- 367 Refinitiv Data Platform Libraries
- 3 Refinitiv Due Diligence
- LSEG Due Diligence Portal API
- 3 Refinitiv Due Dilligence Centre
- Rose's Space
- 1.1K Screening
- 18 Qual-ID API
- 13 Screening Deployed
- 23 Screening Online
- 10 World-Check Customer Risk Screener
- 990 World-Check One
- 44 World-Check One Zero Footprint
- 45 Side by Side Integration API
- Test Space
- 3 Thomson One Smart
- 1.2K TR Internal
- Global Hackathon 2015
- 2 Specialists Who Code
- 10 TR Knowledge Graph
- 150 Transactions
- 142 REDI API
- 1.7K TREP APIs
- 4 CAT
- 21 DACS Station
- 117 Open DACS
- 1.1K RFA
- 103 UPA
- 172 TREP Infrastructure
- 224 TRKD
- 886 TRTH
- 5 Velocity Analytics
- 5 Wealth Management Web Services
- 59 Workspace SDK
- 9 Element Framework
- 5 Grid
- 13 World-Check Data File
- Yield Book Analytics
- 46 中文论坛